import pandas as pd
import numpy as np
import os
from typing import List

data=pd.read_excel('汇总20200415.xlsx')
data=data[['基金名称','投资组合名称','市场','证券代码','股东代码','托管席位','业务类型','发生数量','操作生效日期']]
data.loc[data['业务类型']=='证券增加','direction']=1
data.loc[data['业务类型']=='证券减少','direction']=-1
#print(data)
codelist=list(data['证券代码'].unique())
combilist=list(data['投资组合名称'].unique())
movedDetails=[]
for code in codelist:
    choose={}
    select=data[data['证券代码']==code]
    balance=(select['发生数量']*select['direction']).sum()
    select312=select[select['基金名称']==30012]
    select314=select[select['基金名称']==30014]
    balance312=(select312['发生数量']*data['direction']).sum()
    balance314=(select314['发生数量']*data['direction']).sum()
    #print(f"{code} total:{balance} 312:{balance312} 314:{balance314}")
    for combi in combilist:
       chooseDf=select[select['投资组合名称']==combi]
       if (chooseDf.shape[0]>0):
           choose[combi]=(chooseDf['发生数量']*chooseDf['direction']).sum()
           movedDetails.append({'codeInt':code,'combi':combi,'balance':choose[combi]})
       else:
           choose[combi]=0
movedDetails=pd.DataFrame(data=movedDetails)
movedDetails=movedDetails[movedDetails['balance']!=0]
movedDetails=movedDetails[['codeInt','combi','balance']]
#print(movedDetails)
movedDetails.to_excel('movedDetails20200415.xlsx',index=False)

data=pd.read_excel('312持仓20200416.xls')
position=list(data['T日指令可用数量'])
buypos=list(data['当日买量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
account=list(data['基金名称'])
combi=list(data['组合名称'])
stockholder=list(data['股东代码'])
codeList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
    code=int(stocks[i])
    pos=position[i]
    if (pos<100):
        continue
    #pos=position[i]-buypos[i]
    #pos=np.floor(pos*1/100)*100
    codeStr=str(code).rjust(6,'0')
    acc=int(account[i])
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
        marketname='深交所A'
        seat='061700'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
        marketname='上交所A'
        seat='33211'
    else:
        continue
    mymv=price[i]*pos
    codeList.append({'code':codeStr,'codeInt':code,'account':acc,'combi':combi[i],'market':marketname,'stockholder':stockholder[i],'seat':seat,'hold':pos,'name':names[i],'marketValue':mymv,'price':price[i]})
stocks312=pd.DataFrame(codeList)
stocks312=stocks312[stocks312['stockholder'].isin(['0899018280','D890188317'])]
#print(stocks312)


data=pd.read_excel('314持仓20200416.xls')
position=list(data['持仓'])
buypos=list(data['当日买量'])
stocks=list(data['证券代码'])
names=list(data['证券名称'])
mv=list(data['市值'])
price=list(data['最新价'])
account=list(data['基金名称'])
combi=list(data['组合名称'])
stockholder=list(data['股东代码'])
codeList=[]

for i in range(len(stocks)):
    if np.isnan(stocks[i]):
        continue
    code=int(stocks[i])
    pos=position[i]
    #pos=position[i]-buypos[i]
    #pos=np.floor(pos*1/100)*100
    codeStr=str(code).rjust(6,'0')
    acc=int(account[i])
    if ((codeStr<'009999')|((codeStr>='300000') & (codeStr<='309999'))):
        codeStr+='.SZ'
        marketname='深交所A'
        seat='061700'
    elif ((codeStr>='600000') & (codeStr<='609999')):
        codeStr+='.SH'
        marketname='上交所A'
        seat='33211'
    else:
        continue

    codeList.append({'code':codeStr,'codeInt':code,'account':acc,'combi':combi[i],'market':marketname,'stockholder':stockholder[i],'seat':seat,'hold':pos,'name':names[i],'marketValue':mv[i],'price':price[i]})
stocks314=pd.DataFrame(codeList)

stocksAll=pd.concat([stocks312,stocks314])
codesListAll=list(stocksAll['code'].unique())
combiList=list(stocksAll['combi'].unique())
#print(stocksAll)



####################################################
#check
####################################################
check=[]

for code in codesListAll:
    myhold={}
    mymoved={}
    codeInt=int(code.split('.')[0])
    moved=movedDetails[movedDetails['codeInt']==codeInt]
    myname=stocksAll[stocksAll['code']==code]['name'].iloc[0]
    for combi in combiList:
        select=stocksAll[(stocksAll['combi']==combi) & (stocksAll['code']==code)]
        if (select.shape[0]==1):
            myhold[combi]=select['hold'].iloc[0]
        elif (select.shape[0]==0):
            myhold[combi]=0
        else:
            print(f"{code} {combi} error!")
        select=moved[moved['combi']==combi]
        if (select.shape[0]==1):
            mymoved[combi]=select['balance'].iloc[0]
        elif (select.shape[0]==0):
            mymoved[combi]=0
        else:
            print(f"{code} {combi} moved error!")
        check.append({'股票代码':code,'股票名称':myname,'基金名称':combi,'应有总持仓':myhold[combi]-mymoved[combi],'现有持仓':myhold[combi],'划转':mymoved[combi]})
    pass
check=pd.DataFrame(data=check)
check=check[['股票代码','股票名称','基金名称','应有总持仓','现有持仓','划转']]
print(check)
check.to_excel('status.xlsx',index=False)

#####################################################
#########move
#####################################################
yangList=[
'000878.SZ',
'601166.SH',
'600036.SH',
'002821.SZ',
'300003.SZ',
'002001.SZ',
'600754.SH',
'600809.SH',
'600887.SH',
'600612.SH',
'600741.SH',
'000651.SZ',
'600118.SH',
'002812.SZ',
'600030.SH',
'600837.SH',
'601688.SH',
'601336.SH',
'601318.SH',
'000002.SZ',
'002475.SZ',
]
zhaiList=[
'002415.SZ',
'600030.SH',
'000333.SZ',
'600036.SH',
'600547.SH',
'600519.SH',
'002475.SZ',
'600741.SH',
'600837.SH',
'601888.SH',
'000002.SZ',
'000625.SZ',
'600048.SH',
'600887.SH',
'000001.SZ',
'600068.SH',
'601318.SH',
'603228.SH',
'600900.SH',
'603658.SH',
'002384.SZ',
'300630.SZ',
'603833.SH',
'000961.SZ',
'601021.SH',
'000796.SZ',
'002563.SZ',
]
zhangList=[
'600030.SH',
'000895.SZ',
'601933.SH',
'600690.SH',
'600837.SH',
'601688.SH',
'600887.SH',
'000002.SZ',
'600048.SH',
'600741.SH',
'600276.SH',
'600036.SH',
'000858.SZ',
'600585.SH',
'601166.SH',
'601398.SH',
]

excludeList=['300037.SZ','002373.SZ','002268.SZ','000810.SZ','000009.SZ','600118.SH']

stock80dict={'缺省组合':[],'杨肇宁大宗二级':yangList,'翟淑星大宗二级':zhaiList,'张涵一大宗二级':zhangList}
combi312=list(stocks312['combi'].unique())
combi314=list(stocks314['combi'].unique())
t02ratio=0.25
trytomove=[]
for code in codesListAll:
    if code in excludeList:
        continue
    myhold={}
    mymoved={}
    if code=='000895.SZ':
        print(code)
    codeInt=int(code.split('.')[0])
    price=stocksAll[stocksAll['code']==code]['price'].iloc[0]
    myname=stocksAll[stocksAll['code']==code]['name'].iloc[0]
    marketname=stocksAll[stocksAll['code']==code]['market'].iloc[0]
    mystockholder=stocksAll[stocksAll['code']==code]['stockholder'].iloc[0]
    myseat=stocksAll[stocksAll['code']==code]['seat'].iloc[0]
    moved=movedDetails[movedDetails['codeInt']==codeInt]
    for combi in combiList:
        select=stocksAll[(stocksAll['combi']==combi) & (stocksAll['code']==code)]
        if (select.shape[0]==1):
            myhold[combi]=np.floor(select['hold'].iloc[0]/100)*100
            
        elif (select.shape[0]==0):
            myhold[combi]=0
        else:
            print(f"{code} {combi} error!")
        select=moved[moved['combi']==combi]
        if (select.shape[0]==1):
            mymoved[combi]=select['balance'].iloc[0]
        elif (select.shape[0]==0):
            mymoved[combi]=0
        else:
            print(f"{code} {combi} moved error!")
    canmove={}
    canmovenum=0
    totalmovednum=0
    for combi in combi312:
        mylist=stock80dict[combi]
        totalmovednum=totalmovednum-mymoved[combi]
        if code in mylist:
            canmove[combi]=myhold[combi]
            canmovenum=canmovenum+canmove[combi]
        else:
            alreadmoved=-mymoved[combi]
            if combi=='缺省组合':
                needtomove=np.floor((alreadmoved+myhold[combi])*0.5/100)*100
            else:
                needtomove=np.floor((alreadmoved+myhold[combi])*0.4/100)*100
            needtomove=needtomove-alreadmoved
            if (needtomove<0):
                needtomove=0
            canmove[combi]=needtomove
            canmovenum=canmovenum+canmove[combi]
    totalnum=canmovenum+totalmovednum
    t02=np.round(totalnum*t02ratio-myhold['T02'],-2)
    if (t02<0):
        t02=0
    t01=canmovenum-t02
    if (t01<0):
        t01=0
    if (t01+t02)<1000:
        continue
    for combi in combi312:
        hold312=canmove[combi]
        if (hold312>0):
            if (t02>0):
                if (hold312>t02):
                    #从该组合下部分划入t02
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':t02,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T02','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':t02,'name':myname,'price':price})
                    canmove[combi]=hold312-t02
                    t02=0
                else:
                    #从该组合下全部划入t02
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':hold312,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T02','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':hold312,'name':myname,'price':price})
                    t02=t02-hold312
                    canmove[combi]=0
    for combi in combi312:
        hold312=canmove[combi]
        if (hold312>0):
            if (t01>0):
                if (hold312>t01):
                    #从该组合下部分划入t01
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':t01,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T01','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':t01,'name':myname,'price':price})
                    canmove[combi]=hold312-t01
                    t01=0
                else:
                    #从该组合下全部划入t01
                    trytomove.append({'code':code,'基金名称':30012,'投资组合名称':combi,'市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券减少','发生数量':hold312,'name':myname,'price':price})
                    trytomove.append({'code':code,'基金名称':30014,'投资组合名称':'T01','市场':marketname,'证券代码':code.split('.')[0],'股东代码':mystockholder,'托管席位':myseat,'业务类型':'证券增加','发生数量':hold312,'name':myname,'price':price})
                    t01=t01-hold312
                    canmove[combi]=0
    #print(mymoved)
    #print(myhold)
trytomove=pd.DataFrame(data=trytomove)
trytomove['市值']=trytomove['price']*trytomove['发生数量']
listname=['基金名称','投资组合名称','市场','证券代码','股东代码','托管席位','业务类型','发生数量']
trytomove=trytomove[listname]
trytomove['操作生效日期']=20200417
trytomove['备注']='T0划券'
#trytomove.to_excel('trytomove.xlsx',index=False)
#print(trytomove)
